﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Project_KhuyenMaiVang.Models;
using Project_KhuyenMaiVang.DataProvider;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

namespace Project_KhuyenMaiVang.DAO
{
    public class AccountDAO: MyDataProvider
    {
        public int GetIDAccountFrom(string Username)
        {
            try
            {
                int result;

                string query = "select IDAccount from Account where TinhTrang = 'True' and Username = '" + Username + "'";
                _Adapter = new SqlDataAdapter(query, _Connection);
                DataSet dataset = new DataSet();
                _Adapter.Fill(dataset);
                DataTable dt = dataset.Tables[0];
                result = int.Parse(dt.Rows[0]["IDAccount"].ToString());

                return result;
            }
            catch (System.Exception ex)
            {
                ex.GetBaseException();
                return -1;
            }
        }




        /// <summary>
        /// Get password from IDAccount
        /// </summary>
        /// <param name="IDAccount">int</param>
        /// <returns>string</returns>
        public string GetPasswordFrom(int IDAccount)
        {
            try
            {
                string result = "";

                string query = "select Password from Account where TinhTrang = 'True' and IDAccount = " + IDAccount.ToString();
                _Adapter = new SqlDataAdapter(query, _Connection);
                DataSet dataset = new DataSet();
                _Adapter.Fill(dataset);
                DataTable dt = dataset.Tables[0];
                result = dt.Rows[0]["Password"].ToString();

                return result;
            }
            catch (System.Exception ex)
            {
                ex.GetBaseException();
                return null;   
            }
        }


        // Thêm tài khoản vào database
        // return true nếu thành công, false nếu thất bại
        public bool insertInto(AccountDTO account)
        {
            bool isOK = true;
            try
            {
                string insertCommand = "INSERT INTO Account(Username, Password, MaNhomNguoiDung, TinhTrang) VALUES('" +
                account.UserName + "', '" +
                account.PassWord + "'," +
                account.MaNhomNguoiDung + ",'" +
                account.TinhTrang + "')";

                executeNonQuery(insertCommand);
            }
            catch(Exception e)
            {
                isOK = false;
            }            
            return isOK;
        }

        // Xóa account trong database
        // return true nếu thành công, false nếu thất bại
        public bool delete(AccountDTO account)
        {
            bool isOK = true;
            try
            {
                string deleteCommand = "UPDATE ACCOUNT SET TinhTrang = 'False' WHERE TinhTrang = 'True' and Username = '" + account.UserName + "'";
                executeNonQuery(deleteCommand);
            }
            catch
            {
                isOK = false;
            }
            
            return isOK;
        }


        /// <summary>
        /// Get account by IDAccount
        /// </summary>
        /// <param name="IDAccount"></param>
        /// <returns></returns>
        public AccountDTO getAccount(int IDAccount)
        {
            AccountDTO accountDTO = new AccountDTO();
            try
            {
                string strCommand = "SELECT* FROM ACCOUNT WHERE TinhTrang = 'True' and IDAccount = " + IDAccount.ToString();
                _Adapter = new SqlDataAdapter(strCommand, _Connection);
                DataSet dataset = new DataSet();
                _Adapter.Fill(dataset);
                DataTable dt = dataset.Tables[0];
                object ob = GetDataFromDataRow(dt, 0);
                accountDTO = (AccountDTO)ob;
            }
            catch (Exception ex)
            {
                ex.GetBaseException();
                accountDTO = null;
            }

            return accountDTO;
        }


        /// <summary>
        /// Get account by username
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public AccountDTO getAccount (string username)
        {
            AccountDTO accountDTO = new AccountDTO();
            try
            {
                string strCommand = "SELECT* FROM ACCOUNT WHERE TinhTrang = 'True' and Username = '" + username+ "'";
                _Adapter = new SqlDataAdapter(strCommand, _Connection);
                DataSet dataset = new DataSet();
                _Adapter.Fill(dataset);
                DataTable dt = dataset.Tables[0];
                object ob = GetDataFromDataRow(dt, 0);
                accountDTO = (AccountDTO)ob;
            }
            catch (Exception ex)
            {
                ex.GetBaseException();
                accountDTO = null;
            }

            return accountDTO;
        }
        // Đăng nhập vào trang web
        // return true nếu thành công, false nếu thất bại
        public AccountDTO signIn(AccountDTO infoInput)
        {
            AccountDTO accountDTO = new AccountDTO();
            try
            {
                string strCommand = "SELECT* FROM ACCOUNT WHERE TinhTrang = 'True' and Username = '" + infoInput.UserName + "'and password = '" + infoInput.PassWord+"'";
                _Adapter = new SqlDataAdapter(strCommand, _Connection);
                DataSet dataset = new DataSet();
                _Adapter.Fill(dataset);
                DataTable dt = dataset.Tables[0];
                object ob = GetDataFromDataRow(dt, 0);
                accountDTO = (AccountDTO)ob;
            }
            catch(Exception ex)
            {
                accountDTO = null;
            }

            return accountDTO;
        }

        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            AccountDTO accountDTO = new AccountDTO();
            accountDTO.IDAccount = Int32.Parse(dt.Rows[i]["IDAccount"].ToString());
            accountDTO.UserName = dt.Rows[i]["Username"].ToString();
            accountDTO.PassWord = dt.Rows[i]["Password"].ToString();
            accountDTO.MaNhomNguoiDung = Int32.Parse(dt.Rows[i]["MaNhomNguoiDung"].ToString());
            accountDTO.TinhTrang = bool.Parse(dt.Rows[i]["TinhTrang"].ToString());

            return (object)accountDTO;
        }

        // Cập nhật thông tin account gồm thông tin cá nhân và mật khẩu, không đổi username và email
        // return true nếu thành công, false nếu thất bại 
        public bool updateAccountInfo(AccountDTO infoInput)
        {
            bool isOK = true;
            try
            {
                string updateCommand = "UPDATE ACCOUNT " +
                                        " SET USERNAME = '" + infoInput.UserName + "', " +
                                        " PASSWORD = '" + infoInput.PassWord + "', " +
                                        " MANHOMNGUOIDUNG = " + infoInput.MaNhomNguoiDung + "," +
                                        " TINHTRANG = " + infoInput.TinhTrang
                                        + " where TinhTrang = 'True' and IDAccount = " + infoInput.IDAccount.ToString();

                executeNonQuery(updateCommand);
            }
            catch
            {
                isOK = false;
            }

            return isOK;
        }

        /// <summary>
        /// Update account password by ID
        /// </summary>
        /// <param name="IDAccount">int</param>
        /// <param name="NewPassword">string</param>
        /// <returns>bool</returns>
        public bool updateAccountInfo(int IDAccount, string NewPassword)
        {
            bool isOK = true;
            try
            {
                string updateCommand = "UPDATE ACCOUNT " +
                                        "SET PASSWORD = '" + NewPassword + "' "+
                                        "where TinhTrang = 'True' and IDAccount = " + IDAccount.ToString();

                executeNonQuery(updateCommand);
            }
            catch
            {
                isOK = false;
            }

            return isOK;
        }


        // Tìm kiếm Account dựa theo IDAccount
        // parameter    : IDAccount
        // return       : AccountDTO
        public AccountDTO findWithIDAccount(int idAccount)
        {
            AccountDTO accountDTO = new AccountDTO();
            try
            {
                string strCommand = "SELECT* FROM ACCOUNT WHERE TinhTrang = 'True' and IDAccount = '" + idAccount + "'";
                _Adapter = new SqlDataAdapter(strCommand, _Connection);
                DataSet dataset = new DataSet();
                _Adapter.Fill(dataset);
                DataTable dt = dataset.Tables[0];
                object ob = GetDataFromDataRow(dt, 0);
                accountDTO = (AccountDTO)ob;
            }
            catch (Exception ex)
            {
                accountDTO = null;
            }

            return accountDTO;
        }

        // Tìm kiếm Account dựa theo Mã loại người dùng
        // parameter    : mã loại người dùng
        // return       : List<AccountDTO>
        public List<AccountDTO> findWithUserType(int idUserType)
        {
            List<AccountDTO> result = new List<AccountDTO>();
            try
            {
                string strCommand = "SELECT* FROM ACCOUNT WHERE TinhTrang = 'True' and MaNhomNguoiDung = " + idUserType.ToString();
                _Adapter = new SqlDataAdapter(strCommand, _Connection);
                DataSet dataset = new DataSet();
                _Adapter.Fill(dataset);
                DataTable dt = dataset.Tables[0];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    object ob = GetDataFromDataRow(dt, i);
                    result.Add((AccountDTO)ob);
                }      
            }
            catch (Exception ex)
            {
                result = null;
            }

            return result;
        }

    }
}